DROP INDEX DAM.DAM_DIMENSIONS_PK;

CREATE UNIQUE INDEX DAM.DAM_DIMENSIONS_PK ON DAM.DAM_DIMENSIONS
(DIMENSION_ID)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


DROP INDEX DAM.DAM_DIMENSIONS_U01;

CREATE UNIQUE INDEX DAM.DAM_DIMENSIONS_U01 ON DAM.DAM_DIMENSIONS
(NAME)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


DROP INDEX DAM.DAM_DIMENSION_FIELDS_PK;

CREATE UNIQUE INDEX DAM.DAM_DIMENSION_FIELDS_PK ON DAM.DAM_DIMENSION_FIELDS
(DIMENSION_FIELD_ID)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


DROP INDEX DAM.DAM_DIMENSION_FIELDS_U01;

CREATE UNIQUE INDEX DAM.DAM_DIMENSION_FIELDS_U01 ON DAM.DAM_DIMENSION_FIELDS
(DIMENSION_FK, FIELD_NAME)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


DROP INDEX DAM.DAM_FLOWS_PK;

CREATE UNIQUE INDEX DAM.DAM_FLOWS_PK ON DAM.DAM_FLOWS
(FLOW_ID)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


DROP INDEX DAM.DAM_FLOWS_U01;

CREATE UNIQUE INDEX DAM.DAM_FLOWS_U01 ON DAM.DAM_FLOWS
(FLOW_NAME, PARENT_FLOW_FK)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


DROP INDEX DAM.DAM_FLOW_FUNCTIONS_PK;

CREATE UNIQUE INDEX DAM.DAM_FLOW_FUNCTIONS_PK ON DAM.DAM_FLOW_FUNCTIONS
(FLOW_FK, FUNCTION_FK)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


DROP INDEX DAM.DAM_FUNCTIONS_PK;

CREATE UNIQUE INDEX DAM.DAM_FUNCTIONS_PK ON DAM.DAM_FUNCTIONS
(FUNCTION_ID)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


DROP INDEX DAM.DAM_FUNCTIONS_U01;

CREATE UNIQUE INDEX DAM.DAM_FUNCTIONS_U01 ON DAM.DAM_FUNCTIONS
(NAME, NAMESPACE_FK)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


DROP INDEX DAM.DAM_FUNCTION_FIELDS_PK;

CREATE UNIQUE INDEX DAM.DAM_FUNCTION_FIELDS_PK ON DAM.DAM_FUNCTION_FIELDS
(FUNCTION_FIELD_ID)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


DROP INDEX DAM.DAM_FUNCTION_FIELDS_U01;

CREATE UNIQUE INDEX DAM.DAM_FUNCTION_FIELDS_U01 ON DAM.DAM_FUNCTION_FIELDS
(FUNCTION_FK, DB_FIELD_NAME)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


DROP INDEX DAM.DAM_FUNCTION_FIELDS_U02;

CREATE UNIQUE INDEX DAM.DAM_FUNCTION_FIELDS_U02 ON DAM.DAM_FUNCTION_FIELDS
(FUNCTION_FK, FIELD_NAME)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


DROP INDEX DAM.DAM_FUNCTION_JOINS_PK;

CREATE UNIQUE INDEX DAM.DAM_FUNCTION_JOINS_PK ON DAM.DAM_FUNCTION_JOINS
(FUNCTION_FK, LEFT_FIELD_FK, RIGHT_FIELD_FK)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


DROP INDEX DAM.DAM_FUNCTION_OPSETS_PK;

CREATE UNIQUE INDEX DAM.DAM_FUNCTION_OPSETS_PK ON DAM.DAM_FUNCTION_OPSETS
(OPSET_ID)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


DROP INDEX DAM.DAM_FUNCTION_OPSET_FIELDS_PK;

CREATE UNIQUE INDEX DAM.DAM_FUNCTION_OPSET_FIELDS_PK ON DAM.DAM_FUNCTION_OPSET_FIELDS
(OPSET_FIELD_ID)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


DROP INDEX DAM.DAM_FUNCTION_REF_PK;

CREATE UNIQUE INDEX DAM.DAM_FUNCTION_REF_PK ON DAM.DAM_FUNCTION_REF
(FUNCTION_FK, USES_FUNCTION_FK)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


DROP INDEX DAM.DAM_NAMESPACES_PK;

CREATE UNIQUE INDEX DAM.DAM_NAMESPACES_PK ON DAM.DAM_NAMESPACES
(NAMESPACE_ID)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


DROP INDEX DAM.DAM_NAMESPACES_U01;

CREATE UNIQUE INDEX DAM.DAM_NAMESPACES_U01 ON DAM.DAM_NAMESPACES
(PARENT_NAMESPACE_FK, DESCRIPTION)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;



ALTER TABLE DAM.DAM_DIMENSION_FIELDS
  DROP CONSTRAINT DAM_DIMENSION_FIELDS_R01;

ALTER TABLE DAM.DAM_DIMENSION_FIELDS
  DROP CONSTRAINT DAM_DIMENSION_FIELDS_C02;

ALTER TABLE DAM.DAM_DIMENSION_FIELDS
  DROP CONSTRAINT DAM_DIMENSION_FIELDS_C01;

ALTER TABLE DAM.DAM_DIMENSION_FIELDS
  DROP CONSTRAINT DAM_DIMENSION_FIELDS_PK;

ALTER TABLE DAM.DAM_DIMENSION_FIELDS
  DROP CONSTRAINT DAM_DIMENSION_FIELDS_U01;

ALTER TABLE DAM.DAM_DIMENSION_FIELDS ADD (
  CONSTRAINT DAM_DIMENSION_FIELDS_C02
 CHECK ("DB_FIELD_NAME" IS NOT NULL),
  CONSTRAINT DAM_DIMENSION_FIELDS_C01
 CHECK ("FIELD_NAME" IS NOT NULL),
  CONSTRAINT DAM_DIMENSION_FIELDS_PK
 PRIMARY KEY
 (DIMENSION_FIELD_ID),
  CONSTRAINT DAM_DIMENSION_FIELDS_U01
 UNIQUE (DIMENSION_FK, FIELD_NAME));

ALTER TABLE DAM.DAM_DIMENSIONS
  DROP CONSTRAINT DAM_DIMENSIONS_C01;

ALTER TABLE DAM.DAM_DIMENSIONS
  DROP CONSTRAINT DAM_DIMENSIONS_C02;

ALTER TABLE DAM.DAM_DIMENSIONS
  DROP CONSTRAINT DAM_DIMENSIONS_PK;

ALTER TABLE DAM.DAM_DIMENSIONS
  DROP CONSTRAINT DAM_DIMENSIONS_U01;

ALTER TABLE DAM.DAM_DIMENSIONS ADD (
  CONSTRAINT DAM_DIMENSIONS_C01
 CHECK ("NAME" IS NOT NULL),
  CONSTRAINT DAM_DIMENSIONS_C02
 CHECK ("TABLE_NAME" IS NOT NULL),
  CONSTRAINT DAM_DIMENSIONS_PK
 PRIMARY KEY
 (DIMENSION_ID),
  CONSTRAINT DAM_DIMENSIONS_U01
 UNIQUE (NAME));

ALTER TABLE DAM.DAM_FLOWS
  DROP CONSTRAINT DAM_FLOWS_R01;

ALTER TABLE DAM.DAM_FLOWS
  DROP CONSTRAINT DAM_FLOWS_C01;

ALTER TABLE DAM.DAM_FLOWS
  DROP CONSTRAINT DAM_FLOWS_PK;

ALTER TABLE DAM.DAM_FLOWS
  DROP CONSTRAINT DAM_FLOWS_U01;

ALTER TABLE DAM.DAM_FLOWS ADD (
  CONSTRAINT DAM_FLOWS_C01
 CHECK ("FLOW_NAME" IS NOT NULL),
  CONSTRAINT DAM_FLOWS_PK
 PRIMARY KEY
 (FLOW_ID),
  CONSTRAINT DAM_FLOWS_U01
 UNIQUE (FLOW_NAME, PARENT_FLOW_FK));

ALTER TABLE DAM.DAM_FUNCTION_FIELDS
  DROP CONSTRAINT DAM_FUNCTION_FIELDS_R02;

ALTER TABLE DAM.DAM_FUNCTION_FIELDS
  DROP CONSTRAINT DAM_FUNCTION_FIELDS_R03;

ALTER TABLE DAM.DAM_FUNCTION_FIELDS
  DROP CONSTRAINT DAM_FUNCTION_FIELDS_R01;

ALTER TABLE DAM.DAM_FUNCTION_FIELDS
  DROP CONSTRAINT DAM_FUNCTION_FIELDS_C01;

ALTER TABLE DAM.DAM_FUNCTION_FIELDS
  DROP CONSTRAINT DAM_FUNCTION_FIELDS_C02;

ALTER TABLE DAM.DAM_FUNCTION_FIELDS
  DROP CONSTRAINT DAM_FUNCTION_FIELDS_C03;

ALTER TABLE DAM.DAM_FUNCTION_FIELDS
  DROP CONSTRAINT DAM_FUNCTION_FIELDS_PK;

ALTER TABLE DAM.DAM_FUNCTION_FIELDS
  DROP CONSTRAINT DAM_FUNCTION_FIELDS_U02;

ALTER TABLE DAM.DAM_FUNCTION_FIELDS
  DROP CONSTRAINT DAM_FUNCTION_FIELDS_U01;

ALTER TABLE DAM.DAM_FUNCTION_FIELDS ADD (
  CONSTRAINT DAM_FUNCTION_FIELDS_C01
 CHECK ("FIELD_NAME" IS NOT NULL),
  CONSTRAINT DAM_FUNCTION_FIELDS_C02
 CHECK ("DB_FIELD_NAME" IS NOT NULL),
  CONSTRAINT DAM_FUNCTION_FIELDS_C03
 CHECK ("FUNCTION_FK" IS NOT NULL),
  CONSTRAINT DAM_FUNCTION_FIELDS_PK
 PRIMARY KEY
 (FUNCTION_FIELD_ID),
  CONSTRAINT DAM_FUNCTION_FIELDS_U02
 UNIQUE (FUNCTION_FK, FIELD_NAME),
  CONSTRAINT DAM_FUNCTION_FIELDS_U01
 UNIQUE (FUNCTION_FK, DB_FIELD_NAME));

ALTER TABLE DAM.DAM_FUNCTION_JOINS
  DROP CONSTRAINT DAM_FUNCTION_JOINS_R01;

ALTER TABLE DAM.DAM_FUNCTION_JOINS
  DROP CONSTRAINT DAM_FUNCTION_JOINS_R02;

ALTER TABLE DAM.DAM_FUNCTION_JOINS
  DROP CONSTRAINT DAM_FUNCTION_JOINS_R03;

ALTER TABLE DAM.DAM_FUNCTION_JOINS
  DROP CONSTRAINT DAM_FUNCTION_JOINS_C01;

ALTER TABLE DAM.DAM_FUNCTION_JOINS
  DROP CONSTRAINT DAM_FUNCTION_JOINS_C02;

ALTER TABLE DAM.DAM_FUNCTION_JOINS
  DROP CONSTRAINT DAM_FUNCTION_JOINS_C03;

ALTER TABLE DAM.DAM_FUNCTION_JOINS
  DROP CONSTRAINT DAM_FUNCTION_JOINS_C04;

ALTER TABLE DAM.DAM_FUNCTION_JOINS
  DROP CONSTRAINT DAM_FUNCTION_JOINS_PK;

ALTER TABLE DAM.DAM_FUNCTION_JOINS ADD (
  CONSTRAINT DAM_FUNCTION_JOINS_C01
 CHECK ("FUNCTION_FK" IS NOT NULL),
  CONSTRAINT DAM_FUNCTION_JOINS_C02
 CHECK ("LEFT_FIELD_FK" IS NOT NULL),
  CONSTRAINT DAM_FUNCTION_JOINS_C03
 CHECK ("RIGHT_FIELD_FK" IS NOT NULL),
  CONSTRAINT DAM_FUNCTION_JOINS_C04
 CHECK ("JOIN_TYPE" IS NOT NULL),
  CONSTRAINT DAM_FUNCTION_JOINS_PK
 PRIMARY KEY
 (FUNCTION_FK, LEFT_FIELD_FK, RIGHT_FIELD_FK));

ALTER TABLE DAM.DAM_FUNCTION_OPSET_FIELDS
  DROP CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_R02;

ALTER TABLE DAM.DAM_FUNCTION_OPSET_FIELDS
  DROP CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_R01;

ALTER TABLE DAM.DAM_FUNCTION_OPSET_FIELDS
  DROP CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_C01;

ALTER TABLE DAM.DAM_FUNCTION_OPSET_FIELDS
  DROP CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_C02;

ALTER TABLE DAM.DAM_FUNCTION_OPSET_FIELDS
  DROP CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_PK;

ALTER TABLE DAM.DAM_FUNCTION_OPSET_FIELDS ADD (
  CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_C01
 CHECK ("FUNCTION_OPSET_FK" IS NOT NULL),
  CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_C02
 CHECK ("SEQ" IS NOT NULL),
  CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_PK
 PRIMARY KEY
 (OPSET_FIELD_ID));

ALTER TABLE DAM.DAM_FUNCTION_OPSETS
  DROP CONSTRAINT DAM_FUNCTION_OPSETS_R01;

ALTER TABLE DAM.DAM_FUNCTION_OPSETS
  DROP CONSTRAINT DAM_FUNCTION_OPSETS_R02;

ALTER TABLE DAM.DAM_FUNCTION_OPSETS
  DROP CONSTRAINT DAM_FUNCTION_OPSETS_C01;

ALTER TABLE DAM.DAM_FUNCTION_OPSETS
  DROP CONSTRAINT DAM_FUNCTION_OPSETS_C02;

ALTER TABLE DAM.DAM_FUNCTION_OPSETS
  DROP CONSTRAINT DAM_FUNCTION_OPSETS_C03;

ALTER TABLE DAM.DAM_FUNCTION_OPSETS
  DROP CONSTRAINT DAM_FUNCTION_OPSETS_PK;

ALTER TABLE DAM.DAM_FUNCTION_OPSETS ADD (
  CONSTRAINT DAM_FUNCTION_OPSETS_C01
 CHECK ("FUNCTION_FK" IS NOT NULL),
  CONSTRAINT DAM_FUNCTION_OPSETS_C02
 CHECK ("OPSET" IS NOT NULL),
  CONSTRAINT DAM_FUNCTION_OPSETS_C03
 CHECK ("OPSET_FUNCTION_FK" IS NOT NULL),
  CONSTRAINT DAM_FUNCTION_OPSETS_PK
 PRIMARY KEY
 (OPSET_ID));

ALTER TABLE DAM.DAM_FUNCTIONS
  DROP CONSTRAINT DAM_FUNCTIONS_R02;

ALTER TABLE DAM.DAM_FUNCTIONS
  DROP CONSTRAINT DAM_FUNCTIONS_R01;

ALTER TABLE DAM.DAM_FUNCTIONS
  DROP CONSTRAINT DAM_FUNCTIONS_C01;

ALTER TABLE DAM.DAM_FUNCTIONS
  DROP CONSTRAINT DAM_FUNCTIONS_C02;

ALTER TABLE DAM.DAM_FUNCTIONS
  DROP CONSTRAINT DAM_FUNCTIONS_C04;

ALTER TABLE DAM.DAM_FUNCTIONS
  DROP CONSTRAINT DAM_FUNCTIONS_C03;

ALTER TABLE DAM.DAM_FUNCTIONS
  DROP CONSTRAINT DAM_FUNCTIONS_PK;

ALTER TABLE DAM.DAM_FUNCTIONS
  DROP CONSTRAINT DAM_FUNCTIONS_U01;

ALTER TABLE DAM.DAM_FUNCTIONS ADD (
  CONSTRAINT DAM_FUNCTIONS_C01
 CHECK ("FUNC_TYPE" IS NOT NULL),
  CONSTRAINT DAM_FUNCTIONS_C02
 CHECK ("NAME" IS NOT NULL),
  CONSTRAINT DAM_FUNCTIONS_C04
 CHECK ("ACTION" IS NOT NULL),
  CONSTRAINT DAM_FUNCTIONS_C03
 CHECK ("TABLE_NAME" IS NOT NULL),
  CONSTRAINT DAM_FUNCTIONS_PK
 PRIMARY KEY
 (FUNCTION_ID),
  CONSTRAINT DAM_FUNCTIONS_U01
 UNIQUE (NAME, NAMESPACE_FK));

ALTER TABLE DAM.DAM_NAMESPACES
  DROP CONSTRAINT DAM_NAMESPACES_R01;

ALTER TABLE DAM.DAM_NAMESPACES
  DROP CONSTRAINT DAM_NAMESPACES_PK;

ALTER TABLE DAM.DAM_NAMESPACES
  DROP CONSTRAINT DAM_NAMESPACES_U01;

ALTER TABLE DAM.DAM_NAMESPACES ADD (
  CONSTRAINT DAM_NAMESPACES_PK
 PRIMARY KEY
 (NAMESPACE_ID),
  CONSTRAINT DAM_NAMESPACES_U01
 UNIQUE (PARENT_NAMESPACE_FK, DESCRIPTION));

ALTER TABLE DAM.DAM_FUNCTION_REF
  DROP CONSTRAINT DAM_FUNCTION_REF_R01;

ALTER TABLE DAM.DAM_FUNCTION_REF
  DROP CONSTRAINT DAM_FUNCTION_REF_R02;

ALTER TABLE DAM.DAM_FUNCTION_REF
  DROP CONSTRAINT DAM_FUNCTION_REF_PK;

ALTER TABLE DAM.DAM_FUNCTION_REF ADD (
  CONSTRAINT DAM_FUNCTION_REF_PK
 PRIMARY KEY
 (FUNCTION_FK, USES_FUNCTION_FK));

ALTER TABLE DAM.DAM_FLOW_FUNCTIONS
  DROP CONSTRAINT DAM_FLOW_FUNCTIONS_R02;

ALTER TABLE DAM.DAM_FLOW_FUNCTIONS
  DROP CONSTRAINT DAM_FLOW_FUNCTIONS_R01;

ALTER TABLE DAM.DAM_FLOW_FUNCTIONS
  DROP CONSTRAINT DAM_FLOW_FUNCTIONS_PK;

ALTER TABLE DAM.DAM_FLOW_FUNCTIONS ADD (
  CONSTRAINT DAM_FLOW_FUNCTIONS_PK
 PRIMARY KEY
 (FLOW_FK, FUNCTION_FK));

ALTER TABLE DAM.DAM_DIMENSION_FIELDS ADD (
  CONSTRAINT DAM_DIMENSION_FIELDS_R01 
 FOREIGN KEY (DIMENSION_FK) 
 REFERENCES DAM.DAM_DIMENSIONS (DIMENSION_ID)
    ON DELETE CASCADE);

ALTER TABLE DAM.DAM_FLOWS ADD (
  CONSTRAINT DAM_FLOWS_R01 
 FOREIGN KEY (PARENT_FLOW_FK) 
 REFERENCES DAM.DAM_FLOWS (FLOW_ID)
    ON DELETE CASCADE);

ALTER TABLE DAM.DAM_FUNCTION_FIELDS ADD (
  CONSTRAINT DAM_FUNCTION_FIELDS_R02 
 FOREIGN KEY (FUNCTION_FK) 
 REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID)
    ON DELETE CASCADE,
  CONSTRAINT DAM_FUNCTION_FIELDS_R03 
 FOREIGN KEY (PARENT_FIELD) 
 REFERENCES DAM.DAM_FUNCTION_FIELDS (FUNCTION_FIELD_ID)
    ON DELETE SET NULL,
  CONSTRAINT DAM_FUNCTION_FIELDS_R01 
 FOREIGN KEY (DIMENSION_FK) 
 REFERENCES DAM.DAM_DIMENSIONS (DIMENSION_ID)
    ON DELETE SET NULL);

ALTER TABLE DAM.DAM_FUNCTION_JOINS ADD (
  CONSTRAINT DAM_FUNCTION_JOINS_R01 
 FOREIGN KEY (FUNCTION_FK) 
 REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID)
    ON DELETE CASCADE,
  CONSTRAINT DAM_FUNCTION_JOINS_R02 
 FOREIGN KEY (LEFT_FIELD_FK) 
 REFERENCES DAM.DAM_FUNCTION_FIELDS (FUNCTION_FIELD_ID)
    ON DELETE CASCADE,
  CONSTRAINT DAM_FUNCTION_JOINS_R03 
 FOREIGN KEY (RIGHT_FIELD_FK) 
 REFERENCES DAM.DAM_FUNCTION_FIELDS (FUNCTION_FIELD_ID)
    ON DELETE CASCADE);

ALTER TABLE DAM.DAM_FUNCTION_OPSET_FIELDS ADD (
  CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_R02 
 FOREIGN KEY (FUNCTION_OPSET_FK) 
 REFERENCES DAM.DAM_FUNCTION_OPSETS (OPSET_ID)
    ON DELETE CASCADE,
  CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_R01 
 FOREIGN KEY (FUNCTION_FIELD_FK) 
 REFERENCES DAM.DAM_FUNCTION_FIELDS (FUNCTION_FIELD_ID)
    ON DELETE SET NULL);

ALTER TABLE DAM.DAM_FUNCTION_OPSETS ADD (
  CONSTRAINT DAM_FUNCTION_OPSETS_R01 
 FOREIGN KEY (FUNCTION_FK) 
 REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID)
    ON DELETE CASCADE,
  CONSTRAINT DAM_FUNCTION_OPSETS_R02 
 FOREIGN KEY (OPSET_FUNCTION_FK) 
 REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID)
    ON DELETE CASCADE);

ALTER TABLE DAM.DAM_FUNCTIONS ADD (
  CONSTRAINT DAM_FUNCTIONS_R02 
 FOREIGN KEY (NAMESPACE_FK) 
 REFERENCES DAM.DAM_NAMESPACES (NAMESPACE_ID),
  CONSTRAINT DAM_FUNCTIONS_R01 
 FOREIGN KEY (FLAT_TREE_OF) 
 REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID));

ALTER TABLE DAM.DAM_NAMESPACES ADD (
  CONSTRAINT DAM_NAMESPACES_R01 
 FOREIGN KEY (PARENT_NAMESPACE_FK) 
 REFERENCES DAM.DAM_NAMESPACES (NAMESPACE_ID)
    ON DELETE CASCADE);

ALTER TABLE DAM.DAM_FUNCTION_REF ADD (
  CONSTRAINT DAM_FUNCTION_REF_R01 
 FOREIGN KEY (FUNCTION_FK) 
 REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID)
    ON DELETE CASCADE,
  CONSTRAINT DAM_FUNCTION_REF_R02 
 FOREIGN KEY (USES_FUNCTION_FK) 
 REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID)
    ON DELETE CASCADE);

ALTER TABLE DAM.DAM_FLOW_FUNCTIONS ADD (
  CONSTRAINT DAM_FLOW_FUNCTIONS_R02 
 FOREIGN KEY (FUNCTION_FK) 
 REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID)
    ON DELETE CASCADE,
  CONSTRAINT DAM_FLOW_FUNCTIONS_R01 
 FOREIGN KEY (FLOW_FK) 
 REFERENCES DAM.DAM_FLOWS (FLOW_ID)
    ON DELETE CASCADE);


DROP SEQUENCE DAM.DAM_FIELD_ID;

CREATE SEQUENCE DAM.DAM_FIELD_ID
  START WITH 69
  MAXVALUE 999999999999999999999999999
  MINVALUE 0
  NOCYCLE
  NOCACHE
  NOORDER;


DROP SEQUENCE DAM.DAM_FLOW_ID;

CREATE SEQUENCE DAM.DAM_FLOW_ID
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;


DROP SEQUENCE DAM.DAM_ID;

CREATE SEQUENCE DAM.DAM_ID
  START WITH 241
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;


DROP SEQUENCE DAM.DAM_LOGS_ID;

CREATE SEQUENCE DAM.DAM_LOGS_ID
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;


DROP SEQUENCE DAM.DAM_OPSETS_ID;

CREATE SEQUENCE DAM.DAM_OPSETS_ID
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;


DROP SEQUENCE DAM.DAM_OPSET_FIELDS_ID;

CREATE SEQUENCE DAM.DAM_OPSET_FIELDS_ID
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;



DROP TRIGGER DAM.DAM_DIM_FIELDS_INSERT_TRIGGER;

CREATE OR REPLACE TRIGGER DAM."DAM_DIM_FIELDS_INSERT_TRIGGER" 
BEFORE INSERT
ON DAM.DAM_DIMENSION_FIELDS 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN

   :NEW.db_field_name := UPPER(:NEW.db_field_name);
   :NEW.Created_On := SYSDATE;
   :NEW.Modified_On := SYSDATE;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END ;
/


DROP TRIGGER DAM.DAM_DIM_FIELDS_UPDATE_TRIGGER;

CREATE OR REPLACE TRIGGER DAM."DAM_DIM_FIELDS_UPDATE_TRIGGER" 
BEFORE UPDATE
OF FIELD_NAME
  ,DB_FIELD_NAME
  ,SEQ
  ,DESCRIPTION
ON DAM.DAM_DIMENSION_FIELDS 
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
   SELECT SYSDATE INTO :NEW.Modified_On FROM dual;
   
   :NEW.DB_FIELD_NAME := UPPER(:NEW.DB_FIELD_NAME);

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DAM_DIM_FIELDS_UPDATE_TRIGGER;
/


DROP TRIGGER DAM.DAM_DIM_INSERT_TRIGGER;

CREATE OR REPLACE TRIGGER DAM."DAM_DIM_INSERT_TRIGGER" 
BEFORE INSERT
ON DAM.DAM_DIMENSIONS 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   /*SELECT DAM_DIMENSIONS_ID.NEXTVAL INTO tmpVar FROM dual; 
   :NEW.DIMENSION_ID := tmpVar;                            
   
   IF NVL(:OLD.table_name,'')='' THEN
    :NEW.table_name := 'DIM_' || TO_CHAR(tmpVar, '000');
   END IF;*/
   
   :NEW.table_name := UPPER(:NEW.table_name);
   :NEW.Created_On := SYSDATE;
   :NEW.Modified_On := SYSDATE;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END ;
/


DROP TRIGGER DAM.DAM_DIM_UPDATE_TRIGGER;

CREATE OR REPLACE TRIGGER DAM.DAM_DIM_UPDATE_TRIGGER
BEFORE UPDATE
OF NAME
  ,TABLE_NAME
  ,DESCRIPTION
ON DAM.DAM_DIMENSIONS 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN       
   :NEW.table_name := UPPER(:NEW.table_name);
   :NEW.Modified_On := SYSDATE;   
  
   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DAM_DIM_INSERT_TRIGGER;
/


DROP TRIGGER DAM.DAM_FLOWS_INSERT_TRIGGER;

CREATE OR REPLACE TRIGGER DAM."DAM_FLOWS_INSERT_TRIGGER" 
BEFORE INSERT
ON DAM.DAM_FLOWS 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
  
   SELECT DAM_FLOW_ID.NEXTVAL, SYSDATE, SYSDATE 
   INTO 
   :NEW.FLOW_ID, :NEW.Created_On, :NEW.Modified_On 
   FROM dual;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DAM_FLOWS_INSERT_TRIGGER;
/


DROP TRIGGER DAM.DAM_FUNC_FIELDS_INSERT_TRIGGER;

CREATE OR REPLACE TRIGGER DAM.DAM_FUNC_FIELDS_INSERT_TRIGGER
BEFORE INSERT
ON DAM.DAM_FUNCTION_FIELDS 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN

   :NEW.db_field_name := UPPER(:NEW.db_field_name);
   :NEW.Created_On := SYSDATE;
   :NEW.Modified_On := SYSDATE;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DAM_FUNC_FIELDS_INSERT_TRIGGER;
/


DROP TRIGGER DAM.DAM_FUNC_FIELDS_UPDATE_TRIGGER;

CREATE OR REPLACE TRIGGER DAM.DAM_FUNC_FIELDS_UPDATE_TRIGGER
BEFORE UPDATE
OF FIELD_NAME
  ,DB_FIELD_NAME
  ,EXPRESSION
  ,DESCRIPTION
  ,SEQ
  ,DIMENSION_FK
  ,PARENT_FIELD
  ,DEL_COMPARE
  ,FIELD_TYPE
ON DAM.DAM_FUNCTION_FIELDS 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
   SELECT SYSDATE INTO :NEW.Modified_On FROM dual;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END ;
/


DROP TRIGGER DAM.DAM_FUNC_INSERT_TRIGGER;

CREATE OR REPLACE TRIGGER DAM."DAM_FUNC_INSERT_TRIGGER" 
BEFORE INSERT
ON DAM.DAM_FUNCTIONS 
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
  /* tmpVar := 0;

   SELECT DAM_FUNCTIONS_ID.NEXTVAL INTO tmpVar FROM dual;
   /*:NEW.FUNCTION_ID := tmpVar;*/
   :NEW.table_name := UPPER(:NEW.table_name);
   :NEW.Created_On := SYSDATE;
   :NEW.Modified_On := SYSDATE;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DAM_FUNC_INSERT_TRIGGER;
/


DROP TRIGGER DAM.DAM_FUNC_UPDATE_TRIGGER;

CREATE OR REPLACE TRIGGER DAM.DAM_FUNC_UPDATE_TRIGGER
BEFORE UPDATE
OF NAME
  ,TABLE_NAME
  ,JOIN_CONDITION
  ,WHERE_CONDITION
  ,HAVING_CONDITION
  ,DESCRIPTION
  ,ACTION
ON DAM.DAM_FUNCTIONS 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN                   
   :NEW.table_name := UPPER(:NEW.table_name);
   :NEW.Modified_On := SYSDATE;   
  
   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DAM_FUNC_UPDATE_TRIGGER;
/


DROP TRIGGER DAM.DAM_LOGS_INSERT_TRIGGER;

CREATE OR REPLACE TRIGGER DAM."DAM_LOGS_INSERT_TRIGGER" 
BEFORE INSERT
ON DAM.DAM_LOGS 
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
                            
   SELECT DAM_LOGS_ID.NEXTVAL INTO :NEW.LOG_ID FROM dual;
                              
   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DAM_LOGS_INSERT_TRIGGER;
/


DROP TRIGGER DAM.DAM_OPSET_FIELD_INSERT_TRIGGER;

CREATE OR REPLACE TRIGGER DAM."DAM_OPSET_FIELD_INSERT_TRIGGER" 
BEFORE INSERT
ON DAM.DAM_FUNCTION_OPSET_FIELDS 
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   SELECT DAM_OPSET_FIELDS_ID.NEXTVAL INTO tmpVar FROM dual;
   :NEW.OPSET_FIELD_ID := tmpVar;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DAM_OPSET_FIELD_INSERT_TRIGGER;
/


DROP TRIGGER DAM.DAM_OPSET_INSERT_TRIGGER;

CREATE OR REPLACE TRIGGER DAM."DAM_OPSET_INSERT_TRIGGER" 
BEFORE INSERT
ON DAM.DAM_FUNCTION_OPSETS 
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   SELECT DAM_OPSETS_ID.NEXTVAL INTO tmpVar FROM dual;
   :NEW.OPSET_ID := tmpVar;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DAM_OPSET_INSERT_TRIGGER;
/



DROP TABLE DAM.DAM_LOGS CASCADE CONSTRAINTS;

CREATE TABLE DAM.DAM_LOGS
(
  LOG_ID             NUMBER(38),
  PART_OF_FLOW       NUMBER(38),
  AFFECTED_FUNCTION  NUMBER(38),
  STARTED_ON         DATE,
  ENDED_ON           DATE,
  ACTION             VARCHAR2(64 BYTE),
  REC_RETURNED       NUMBER(38),
  ERR_CODE           NUMBER(38)
)
TABLESPACE DAM_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


ALTER TABLE DAM.DAM_FLOWS
 DROP PRIMARY KEY CASCADE;
DROP TABLE DAM.DAM_FLOWS CASCADE CONSTRAINTS;

CREATE TABLE DAM.DAM_FLOWS
(
  FLOW_ID         NUMBER(38),
  FLOW_NAME       VARCHAR2(64 BYTE) CONSTRAINT DAM_FLOWS_C01 NOT NULL,
  ANNOTATION      VARCHAR2(4000 BYTE),
  CREATED_ON      DATE,
  MODIFIED_ON     DATE,
  PARENT_FLOW_FK  NUMBER(38)
)
TABLESPACE DAM_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


ALTER TABLE DAM.DAM_DIMENSIONS
 DROP PRIMARY KEY CASCADE;
DROP TABLE DAM.DAM_DIMENSIONS CASCADE CONSTRAINTS;

CREATE TABLE DAM.DAM_DIMENSIONS
(
  DIMENSION_ID  NUMBER(38),
  NAME          VARCHAR2(64 BYTE) CONSTRAINT DAM_DIMENSIONS_C01 NOT NULL,
  TABLE_NAME    VARCHAR2(30 BYTE) CONSTRAINT DAM_DIMENSIONS_C02 NOT NULL,
  DESCRIPTION   VARCHAR2(4000 BYTE),
  MODIFIED_ON   DATE,
  CREATED_ON    DATE
)
TABLESPACE DAM_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


ALTER TABLE DAM.DAM_DIMENSION_FIELDS
 DROP PRIMARY KEY CASCADE;
DROP TABLE DAM.DAM_DIMENSION_FIELDS CASCADE CONSTRAINTS;

CREATE TABLE DAM.DAM_DIMENSION_FIELDS
(
  DIMENSION_FIELD_ID  NUMBER(38),
  FIELD_NAME          VARCHAR2(64 BYTE) CONSTRAINT DAM_DIMENSION_FIELDS_C01 NOT NULL,
  DB_FIELD_NAME       VARCHAR2(30 BYTE) CONSTRAINT DAM_DIMENSION_FIELDS_C02 NOT NULL,
  DIMENSION_FK        NUMBER(38),
  SEQ                 NUMBER(38),
  DESCRIPTION         VARCHAR2(4000 BYTE),
  MODIFIED_ON         DATE,
  CREATED_ON          DATE,
  NUMBER_FORMAT       VARCHAR2(16 BYTE),
  COLUMN_WIDTH        NUMBER                    DEFAULT 64,
  FIELD_TYPE          NUMBER(1)                 DEFAULT 0
)
TABLESPACE DAM_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON COLUMN DAM.DAM_DIMENSION_FIELDS.FIELD_TYPE IS '0=ValueField;1=Key; 2=Description';


ALTER TABLE DAM.DAM_NAMESPACES
 DROP PRIMARY KEY CASCADE;
DROP TABLE DAM.DAM_NAMESPACES CASCADE CONSTRAINTS;

CREATE TABLE DAM.DAM_NAMESPACES
(
  NAMESPACE_ID         NUMBER                   NOT NULL,
  PARENT_NAMESPACE_FK  NUMBER,
  DESCRIPTION          VARCHAR2(64 BYTE)        NOT NULL,
  ANNOTATION           VARCHAR2(4000 BYTE)
)
TABLESPACE DAM_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX DAM.DAM_DIMENSIONS_PK ON DAM.DAM_DIMENSIONS
(DIMENSION_ID)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX DAM.DAM_DIMENSIONS_U01 ON DAM.DAM_DIMENSIONS
(NAME)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX DAM.DAM_DIMENSION_FIELDS_PK ON DAM.DAM_DIMENSION_FIELDS
(DIMENSION_FIELD_ID)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX DAM.DAM_DIMENSION_FIELDS_U01 ON DAM.DAM_DIMENSION_FIELDS
(DIMENSION_FK, FIELD_NAME)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX DAM.DAM_FLOWS_U01 ON DAM.DAM_FLOWS
(FLOW_NAME, PARENT_FLOW_FK)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX DAM.DAM_FLOWS_PK ON DAM.DAM_FLOWS
(FLOW_ID)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX DAM.DAM_NAMESPACES_PK ON DAM.DAM_NAMESPACES
(NAMESPACE_ID)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX DAM.DAM_NAMESPACES_U01 ON DAM.DAM_NAMESPACES
(PARENT_NAMESPACE_FK, DESCRIPTION)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE TRIGGER DAM."DAM_FLOWS_INSERT_TRIGGER" 
BEFORE INSERT
ON DAM.DAM_FLOWS 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
  
   SELECT DAM_FLOW_ID.NEXTVAL, SYSDATE, SYSDATE 
   INTO 
   :NEW.FLOW_ID, :NEW.Created_On, :NEW.Modified_On 
   FROM dual;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DAM_FLOWS_INSERT_TRIGGER;
/


CREATE OR REPLACE TRIGGER DAM."DAM_LOGS_INSERT_TRIGGER" 
BEFORE INSERT
ON DAM.DAM_LOGS 
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
                            
   SELECT DAM_LOGS_ID.NEXTVAL INTO :NEW.LOG_ID FROM dual;
                              
   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DAM_LOGS_INSERT_TRIGGER;
/


CREATE OR REPLACE TRIGGER DAM."DAM_DIM_INSERT_TRIGGER" 
BEFORE INSERT
ON DAM.DAM_DIMENSIONS 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   /*SELECT DAM_DIMENSIONS_ID.NEXTVAL INTO tmpVar FROM dual; 
   :NEW.DIMENSION_ID := tmpVar;                            
   
   IF NVL(:OLD.table_name,'')='' THEN
    :NEW.table_name := 'DIM_' || TO_CHAR(tmpVar, '000');
   END IF;*/
   
   :NEW.table_name := UPPER(:NEW.table_name);
   :NEW.Created_On := SYSDATE;
   :NEW.Modified_On := SYSDATE;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END ;
/


CREATE OR REPLACE TRIGGER DAM."DAM_DIM_FIELDS_UPDATE_TRIGGER" 
BEFORE UPDATE
OF FIELD_NAME
  ,DB_FIELD_NAME
  ,SEQ
  ,DESCRIPTION
ON DAM.DAM_DIMENSION_FIELDS 
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
   SELECT SYSDATE INTO :NEW.Modified_On FROM dual;
   
   :NEW.DB_FIELD_NAME := UPPER(:NEW.DB_FIELD_NAME);

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DAM_DIM_FIELDS_UPDATE_TRIGGER;
/


CREATE OR REPLACE TRIGGER DAM.DAM_DIM_UPDATE_TRIGGER
BEFORE UPDATE
OF NAME
  ,TABLE_NAME
  ,DESCRIPTION
ON DAM.DAM_DIMENSIONS 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN       
   :NEW.table_name := UPPER(:NEW.table_name);
   :NEW.Modified_On := SYSDATE;   
  
   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DAM_DIM_INSERT_TRIGGER;
/


CREATE OR REPLACE TRIGGER DAM."DAM_DIM_FIELDS_INSERT_TRIGGER" 
BEFORE INSERT
ON DAM.DAM_DIMENSION_FIELDS 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN

   :NEW.db_field_name := UPPER(:NEW.db_field_name);
   :NEW.Created_On := SYSDATE;
   :NEW.Modified_On := SYSDATE;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END ;
/


ALTER TABLE DAM.DAM_FUNCTIONS
 DROP PRIMARY KEY CASCADE;
DROP TABLE DAM.DAM_FUNCTIONS CASCADE CONSTRAINTS;

CREATE TABLE DAM.DAM_FUNCTIONS
(
  FUNCTION_ID       NUMBER(38),
  NAME              VARCHAR2(64 BYTE) CONSTRAINT DAM_FUNCTIONS_C02 NOT NULL,
  TABLE_NAME        VARCHAR2(30 BYTE) CONSTRAINT DAM_FUNCTIONS_C03 NOT NULL,
  JOIN_CONDITION    VARCHAR2(2048 BYTE),
  WHERE_CONDITION   VARCHAR2(2048 BYTE),
  HAVING_CONDITION  VARCHAR2(2048 BYTE),
  DESCRIPTION       VARCHAR2(4000 BYTE),
  MODIFIED_ON       DATE                        NOT NULL,
  CREATED_ON        DATE                        NOT NULL,
  FUNC_TYPE         NUMBER(38) CONSTRAINT DAM_FUNCTIONS_C01 NOT NULL,
  FLAT_TREE_OF      NUMBER(38),
  LOCKED_BY         VARCHAR2(32 BYTE)           DEFAULT NULL,
  ACTION            NUMBER(38)                  DEFAULT 0 CONSTRAINT DAM_FUNCTIONS_C04 NOT NULL,
  NAMESPACE_FK      NUMBER
)
TABLESPACE DAM_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON COLUMN DAM.DAM_FUNCTIONS.NAME IS 'Function Name';

COMMENT ON COLUMN DAM.DAM_FUNCTIONS.TABLE_NAME IS 'Name of physical Table';

COMMENT ON COLUMN DAM.DAM_FUNCTIONS.JOIN_CONDITION IS 'SQL formated join condition';

COMMENT ON COLUMN DAM.DAM_FUNCTIONS.WHERE_CONDITION IS 'SQL style where condition';

COMMENT ON COLUMN DAM.DAM_FUNCTIONS.HAVING_CONDITION IS 'SQL style having condition';

COMMENT ON COLUMN DAM.DAM_FUNCTIONS.DESCRIPTION IS 'Comment field';

COMMENT ON COLUMN DAM.DAM_FUNCTIONS.FUNC_TYPE IS '0=INTERNAL;1=INPUT; 2=TABLE; 3=VIEW';

COMMENT ON COLUMN DAM.DAM_FUNCTIONS.FLAT_TREE_OF IS 'if not null, defines that this is a flat tree of the function defined by that fk';

COMMENT ON COLUMN DAM.DAM_FUNCTIONS.LOCKED_BY IS '0=NO; <>0 (Session-ID) (Executed or in use by another user)';

COMMENT ON COLUMN DAM.DAM_FUNCTIONS.ACTION IS '0=TRUNCATE; 1=ADD; 2=MERGE; 3=UPDATE';


ALTER TABLE DAM.DAM_FUNCTION_REF
 DROP PRIMARY KEY CASCADE;
DROP TABLE DAM.DAM_FUNCTION_REF CASCADE CONSTRAINTS;

CREATE TABLE DAM.DAM_FUNCTION_REF
(
  FUNCTION_FK       NUMBER(38),
  USES_FUNCTION_FK  NUMBER(38),
  CREATED_ON        DATE,
  MODIFIED_ON       DATE,
  POS_X             NUMBER(38),
  POS_Y             NUMBER(38),
  ALIAS             VARCHAR2(64 BYTE)           NOT NULL,
  ANNOTATION        VARCHAR2(4000 BYTE)
)
TABLESPACE DAM_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON COLUMN DAM.DAM_FUNCTION_REF.POS_X IS 'horizontal position of table on join-definition area';

COMMENT ON COLUMN DAM.DAM_FUNCTION_REF.POS_Y IS 'vertical position of table on join-definition area';


ALTER TABLE DAM.DAM_FUNCTION_OPSETS
 DROP PRIMARY KEY CASCADE;
DROP TABLE DAM.DAM_FUNCTION_OPSETS CASCADE CONSTRAINTS;

CREATE TABLE DAM.DAM_FUNCTION_OPSETS
(
  OPSET_ID           NUMBER(38),
  FUNCTION_FK        NUMBER(38) CONSTRAINT DAM_FUNCTION_OPSETS_C01 NOT NULL,
  OPSET              VARCHAR2(16 BYTE) CONSTRAINT DAM_FUNCTION_OPSETS_C02 NOT NULL,
  OPSET_FUNCTION_FK  NUMBER(38) CONSTRAINT DAM_FUNCTION_OPSETS_C03 NOT NULL
)
TABLESPACE DAM_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON COLUMN DAM.DAM_FUNCTION_OPSETS.OPSET IS 'Oracle Supported OpSets UNION, UNION ALL, INTERSECT, MINUS';


ALTER TABLE DAM.DAM_FUNCTION_FIELDS
 DROP PRIMARY KEY CASCADE;
DROP TABLE DAM.DAM_FUNCTION_FIELDS CASCADE CONSTRAINTS;

CREATE TABLE DAM.DAM_FUNCTION_FIELDS
(
  FUNCTION_FIELD_ID  NUMBER(38),
  FIELD_NAME         VARCHAR2(64 BYTE) CONSTRAINT DAM_FUNCTION_FIELDS_C01 NOT NULL,
  DB_FIELD_NAME      VARCHAR2(30 BYTE) CONSTRAINT DAM_FUNCTION_FIELDS_C02 NOT NULL,
  EXPRESSION         VARCHAR2(4000 BYTE),
  DESCRIPTION        VARCHAR2(4000 BYTE),
  SEQ                NUMBER(38),
  FUNCTION_FK        NUMBER(38) CONSTRAINT DAM_FUNCTION_FIELDS_C03 NOT NULL,
  DIMENSION_FK       NUMBER(38),
  CREATED_ON         DATE,
  MODIFIED_ON        DATE,
  NUMBER_FORMAT      VARCHAR2(16 BYTE),
  COLUMN_WIDTH       NUMBER                     DEFAULT 64,
  PARENT_FIELD       NUMBER,
  DEL_COMPARE        NUMBER                     DEFAULT 0,
  FIELD_TYPE         NUMBER                     DEFAULT 0
)
TABLESPACE DAM_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON COLUMN DAM.DAM_FUNCTION_FIELDS.DEL_COMPARE IS 'If <>0, then use Field to compare for deletion when function-action is 4 (delete)';

COMMENT ON COLUMN DAM.DAM_FUNCTION_FIELDS.FIELD_TYPE IS '0=Value Field; 1=PK';


ALTER TABLE DAM.DAM_FLOW_FUNCTIONS
 DROP PRIMARY KEY CASCADE;
DROP TABLE DAM.DAM_FLOW_FUNCTIONS CASCADE CONSTRAINTS;

CREATE TABLE DAM.DAM_FLOW_FUNCTIONS
(
  FLOW_FK      NUMBER(38),
  FUNCTION_FK  NUMBER(38),
  IS_ACTIVE    NUMBER(38),
  CREATED_ON   DATE,
  MODIFIED_ON  DATE
)
TABLESPACE DAM_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX DAM.DAM_FLOW_FUNCTIONS_PK ON DAM.DAM_FLOW_FUNCTIONS
(FLOW_FK, FUNCTION_FK)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX DAM.DAM_FUNCTIONS_U01 ON DAM.DAM_FUNCTIONS
(NAME, NAMESPACE_FK)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX DAM.DAM_FUNCTIONS_PK ON DAM.DAM_FUNCTIONS
(FUNCTION_ID)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX DAM.DAM_FUNCTION_FIELDS_PK ON DAM.DAM_FUNCTION_FIELDS
(FUNCTION_FIELD_ID)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX DAM.DAM_FUNCTION_FIELDS_U01 ON DAM.DAM_FUNCTION_FIELDS
(FUNCTION_FK, DB_FIELD_NAME)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX DAM.DAM_FUNCTION_FIELDS_U02 ON DAM.DAM_FUNCTION_FIELDS
(FUNCTION_FK, FIELD_NAME)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX DAM.DAM_FUNCTION_OPSETS_PK ON DAM.DAM_FUNCTION_OPSETS
(OPSET_ID)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX DAM.DAM_FUNCTION_REF_PK ON DAM.DAM_FUNCTION_REF
(FUNCTION_FK, USES_FUNCTION_FK)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE TRIGGER DAM."DAM_OPSET_INSERT_TRIGGER" 
BEFORE INSERT
ON DAM.DAM_FUNCTION_OPSETS 
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   SELECT DAM_OPSETS_ID.NEXTVAL INTO tmpVar FROM dual;
   :NEW.OPSET_ID := tmpVar;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DAM_OPSET_INSERT_TRIGGER;
/


CREATE OR REPLACE TRIGGER DAM."DAM_FUNC_INSERT_TRIGGER" 
BEFORE INSERT
ON DAM.DAM_FUNCTIONS 
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
  /* tmpVar := 0;

   SELECT DAM_FUNCTIONS_ID.NEXTVAL INTO tmpVar FROM dual;
   /*:NEW.FUNCTION_ID := tmpVar;*/
   :NEW.table_name := UPPER(:NEW.table_name);
   :NEW.Created_On := SYSDATE;
   :NEW.Modified_On := SYSDATE;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DAM_FUNC_INSERT_TRIGGER;
/


CREATE OR REPLACE TRIGGER DAM.DAM_FUNC_FIELDS_UPDATE_TRIGGER
BEFORE UPDATE
OF FIELD_NAME
  ,DB_FIELD_NAME
  ,EXPRESSION
  ,DESCRIPTION
  ,SEQ
  ,DIMENSION_FK
  ,PARENT_FIELD
  ,DEL_COMPARE
  ,FIELD_TYPE
ON DAM.DAM_FUNCTION_FIELDS 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
   SELECT SYSDATE INTO :NEW.Modified_On FROM dual;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END ;
/


CREATE OR REPLACE TRIGGER DAM.DAM_FUNC_FIELDS_INSERT_TRIGGER
BEFORE INSERT
ON DAM.DAM_FUNCTION_FIELDS 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN

   :NEW.db_field_name := UPPER(:NEW.db_field_name);
   :NEW.Created_On := SYSDATE;
   :NEW.Modified_On := SYSDATE;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DAM_FUNC_FIELDS_INSERT_TRIGGER;
/


CREATE OR REPLACE TRIGGER DAM.DAM_FUNC_UPDATE_TRIGGER
BEFORE UPDATE
OF NAME
  ,TABLE_NAME
  ,JOIN_CONDITION
  ,WHERE_CONDITION
  ,HAVING_CONDITION
  ,DESCRIPTION
  ,ACTION
ON DAM.DAM_FUNCTIONS 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN                   
   :NEW.table_name := UPPER(:NEW.table_name);
   :NEW.Modified_On := SYSDATE;   
  
   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DAM_FUNC_UPDATE_TRIGGER;
/


ALTER TABLE DAM.DAM_FUNCTION_OPSET_FIELDS
 DROP PRIMARY KEY CASCADE;
DROP TABLE DAM.DAM_FUNCTION_OPSET_FIELDS CASCADE CONSTRAINTS;

CREATE TABLE DAM.DAM_FUNCTION_OPSET_FIELDS
(
  OPSET_FIELD_ID     NUMBER(38),
  FUNCTION_OPSET_FK  NUMBER(38) CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_C01 NOT NULL,
  FUNCTION_FIELD_FK  NUMBER(38),
  SEQ                NUMBER(38) CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_C02 NOT NULL
)
TABLESPACE DAM_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


ALTER TABLE DAM.DAM_FUNCTION_JOINS
 DROP PRIMARY KEY CASCADE;
DROP TABLE DAM.DAM_FUNCTION_JOINS CASCADE CONSTRAINTS;

CREATE TABLE DAM.DAM_FUNCTION_JOINS
(
  FUNCTION_FK     NUMBER(38) CONSTRAINT DAM_FUNCTION_JOINS_C01 NOT NULL,
  LEFT_FIELD_FK   NUMBER(38) CONSTRAINT DAM_FUNCTION_JOINS_C02 NOT NULL,
  RIGHT_FIELD_FK  NUMBER(38) CONSTRAINT DAM_FUNCTION_JOINS_C03 NOT NULL,
  JOIN_TYPE       NUMBER                        DEFAULT 0 CONSTRAINT DAM_FUNCTION_JOINS_C04 NOT NULL
)
TABLESPACE DAM_TABLES
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON COLUMN DAM.DAM_FUNCTION_JOINS.JOIN_TYPE IS '-1=LEFT JOIN;0=INNER JOIN;1=RIGHT JOIN';


CREATE UNIQUE INDEX DAM.DAM_FUNCTION_JOINS_PK ON DAM.DAM_FUNCTION_JOINS
(FUNCTION_FK, LEFT_FIELD_FK, RIGHT_FIELD_FK)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX DAM.DAM_FUNCTION_OPSET_FIELDS_PK ON DAM.DAM_FUNCTION_OPSET_FIELDS
(OPSET_FIELD_ID)
LOGGING
TABLESPACE DAM_TABLES
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE OR REPLACE TRIGGER DAM."DAM_OPSET_FIELD_INSERT_TRIGGER" 
BEFORE INSERT
ON DAM.DAM_FUNCTION_OPSET_FIELDS 
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
   tmpVar := 0;

   SELECT DAM_OPSET_FIELDS_ID.NEXTVAL INTO tmpVar FROM dual;
   :NEW.OPSET_FIELD_ID := tmpVar;

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END DAM_OPSET_FIELD_INSERT_TRIGGER;
/


ALTER TABLE DAM.DAM_FLOWS ADD (
  CONSTRAINT DAM_FLOWS_PK
 PRIMARY KEY
 (FLOW_ID)
    USING INDEX 
    TABLESPACE DAM_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ),
  CONSTRAINT DAM_FLOWS_U01
 UNIQUE (FLOW_NAME, PARENT_FLOW_FK)
    USING INDEX 
    TABLESPACE DAM_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE DAM.DAM_DIMENSIONS ADD (
  CONSTRAINT DAM_DIMENSIONS_PK
 PRIMARY KEY
 (DIMENSION_ID)
    USING INDEX 
    TABLESPACE DAM_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ),
  CONSTRAINT DAM_DIMENSIONS_U01
 UNIQUE (NAME)
    USING INDEX 
    TABLESPACE DAM_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE DAM.DAM_DIMENSION_FIELDS ADD (
  CONSTRAINT DAM_DIMENSION_FIELDS_PK
 PRIMARY KEY
 (DIMENSION_FIELD_ID)
    USING INDEX 
    TABLESPACE DAM_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ),
  CONSTRAINT DAM_DIMENSION_FIELDS_U01
 UNIQUE (DIMENSION_FK, FIELD_NAME)
    USING INDEX 
    TABLESPACE DAM_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE DAM.DAM_NAMESPACES ADD (
  CONSTRAINT DAM_NAMESPACES_PK
 PRIMARY KEY
 (NAMESPACE_ID)
    USING INDEX 
    TABLESPACE DAM_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ),
  CONSTRAINT DAM_NAMESPACES_U01
 UNIQUE (PARENT_NAMESPACE_FK, DESCRIPTION)
    USING INDEX 
    TABLESPACE DAM_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE DAM.DAM_FUNCTIONS ADD (
  CONSTRAINT DAM_FUNCTIONS_PK
 PRIMARY KEY
 (FUNCTION_ID)
    USING INDEX 
    TABLESPACE DAM_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ),
  CONSTRAINT DAM_FUNCTIONS_U01
 UNIQUE (NAME, NAMESPACE_FK)
    USING INDEX 
    TABLESPACE DAM_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE DAM.DAM_FUNCTION_REF ADD (
  CONSTRAINT DAM_FUNCTION_REF_PK
 PRIMARY KEY
 (FUNCTION_FK, USES_FUNCTION_FK)
    USING INDEX 
    TABLESPACE DAM_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE DAM.DAM_FUNCTION_OPSETS ADD (
  CONSTRAINT DAM_FUNCTION_OPSETS_PK
 PRIMARY KEY
 (OPSET_ID)
    USING INDEX 
    TABLESPACE DAM_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE DAM.DAM_FUNCTION_FIELDS ADD (
  CONSTRAINT DAM_FUNCTION_FIELDS_PK
 PRIMARY KEY
 (FUNCTION_FIELD_ID)
    USING INDEX 
    TABLESPACE DAM_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ),
  CONSTRAINT DAM_FUNCTION_FIELDS_U01
 UNIQUE (FUNCTION_FK, DB_FIELD_NAME)
    USING INDEX 
    TABLESPACE DAM_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ),
  CONSTRAINT DAM_FUNCTION_FIELDS_U02
 UNIQUE (FUNCTION_FK, FIELD_NAME)
    USING INDEX 
    TABLESPACE DAM_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE DAM.DAM_FLOW_FUNCTIONS ADD (
  CONSTRAINT DAM_FLOW_FUNCTIONS_PK
 PRIMARY KEY
 (FLOW_FK, FUNCTION_FK)
    USING INDEX 
    TABLESPACE DAM_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE DAM.DAM_FUNCTION_OPSET_FIELDS ADD (
  CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_PK
 PRIMARY KEY
 (OPSET_FIELD_ID)
    USING INDEX 
    TABLESPACE DAM_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE DAM.DAM_FUNCTION_JOINS ADD (
  CONSTRAINT DAM_FUNCTION_JOINS_PK
 PRIMARY KEY
 (FUNCTION_FK, LEFT_FIELD_FK, RIGHT_FIELD_FK)
    USING INDEX 
    TABLESPACE DAM_TABLES
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE DAM.DAM_FLOWS ADD (
  CONSTRAINT DAM_FLOWS_R01 
 FOREIGN KEY (PARENT_FLOW_FK) 
 REFERENCES DAM.DAM_FLOWS (FLOW_ID)
    ON DELETE CASCADE);

ALTER TABLE DAM.DAM_DIMENSION_FIELDS ADD (
  CONSTRAINT DAM_DIMENSION_FIELDS_R01 
 FOREIGN KEY (DIMENSION_FK) 
 REFERENCES DAM.DAM_DIMENSIONS (DIMENSION_ID)
    ON DELETE CASCADE);

ALTER TABLE DAM.DAM_NAMESPACES ADD (
  CONSTRAINT DAM_NAMESPACES_R01 
 FOREIGN KEY (PARENT_NAMESPACE_FK) 
 REFERENCES DAM.DAM_NAMESPACES (NAMESPACE_ID)
    ON DELETE CASCADE);

ALTER TABLE DAM.DAM_FUNCTIONS ADD (
  CONSTRAINT DAM_FUNCTIONS_R01 
 FOREIGN KEY (FLAT_TREE_OF) 
 REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID),
  CONSTRAINT DAM_FUNCTIONS_R02 
 FOREIGN KEY (NAMESPACE_FK) 
 REFERENCES DAM.DAM_NAMESPACES (NAMESPACE_ID));

ALTER TABLE DAM.DAM_FUNCTION_REF ADD (
  CONSTRAINT DAM_FUNCTION_REF_R01 
 FOREIGN KEY (FUNCTION_FK) 
 REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID)
    ON DELETE CASCADE,
  CONSTRAINT DAM_FUNCTION_REF_R02 
 FOREIGN KEY (USES_FUNCTION_FK) 
 REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID)
    ON DELETE CASCADE);

ALTER TABLE DAM.DAM_FUNCTION_OPSETS ADD (
  CONSTRAINT DAM_FUNCTION_OPSETS_R01 
 FOREIGN KEY (FUNCTION_FK) 
 REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID)
    ON DELETE CASCADE,
  CONSTRAINT DAM_FUNCTION_OPSETS_R02 
 FOREIGN KEY (OPSET_FUNCTION_FK) 
 REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID)
    ON DELETE CASCADE);

ALTER TABLE DAM.DAM_FUNCTION_FIELDS ADD (
  CONSTRAINT DAM_FUNCTION_FIELDS_R01 
 FOREIGN KEY (DIMENSION_FK) 
 REFERENCES DAM.DAM_DIMENSIONS (DIMENSION_ID)
    ON DELETE SET NULL,
  CONSTRAINT DAM_FUNCTION_FIELDS_R02 
 FOREIGN KEY (FUNCTION_FK) 
 REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID)
    ON DELETE CASCADE,
  CONSTRAINT DAM_FUNCTION_FIELDS_R03 
 FOREIGN KEY (PARENT_FIELD) 
 REFERENCES DAM.DAM_FUNCTION_FIELDS (FUNCTION_FIELD_ID)
    ON DELETE SET NULL);

ALTER TABLE DAM.DAM_FLOW_FUNCTIONS ADD (
  CONSTRAINT DAM_FLOW_FUNCTIONS_R01 
 FOREIGN KEY (FLOW_FK) 
 REFERENCES DAM.DAM_FLOWS (FLOW_ID)
    ON DELETE CASCADE,
  CONSTRAINT DAM_FLOW_FUNCTIONS_R02 
 FOREIGN KEY (FUNCTION_FK) 
 REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID)
    ON DELETE CASCADE);

ALTER TABLE DAM.DAM_FUNCTION_OPSET_FIELDS ADD (
  CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_R01 
 FOREIGN KEY (FUNCTION_FIELD_FK) 
 REFERENCES DAM.DAM_FUNCTION_FIELDS (FUNCTION_FIELD_ID)
    ON DELETE SET NULL,
  CONSTRAINT DAM_FUNCTION_OPSET_FIELDS_R02 
 FOREIGN KEY (FUNCTION_OPSET_FK) 
 REFERENCES DAM.DAM_FUNCTION_OPSETS (OPSET_ID)
    ON DELETE CASCADE);

ALTER TABLE DAM.DAM_FUNCTION_JOINS ADD (
  CONSTRAINT DAM_FUNCTION_JOINS_R01 
 FOREIGN KEY (FUNCTION_FK) 
 REFERENCES DAM.DAM_FUNCTIONS (FUNCTION_ID)
    ON DELETE CASCADE,
  CONSTRAINT DAM_FUNCTION_JOINS_R02 
 FOREIGN KEY (LEFT_FIELD_FK) 
 REFERENCES DAM.DAM_FUNCTION_FIELDS (FUNCTION_FIELD_ID)
    ON DELETE CASCADE,
  CONSTRAINT DAM_FUNCTION_JOINS_R03 
 FOREIGN KEY (RIGHT_FIELD_FK) 
 REFERENCES DAM.DAM_FUNCTION_FIELDS (FUNCTION_FIELD_ID)
    ON DELETE CASCADE);


DROP PACKAGE DAM.DAM_CORE;

CREATE OR REPLACE PACKAGE DAM."DAM_CORE" AS
/******************************************************************************
   NAME:       DAM_CORE
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        12.12.2007  FUCHSST          1. Created this package.
******************************************************************************/

  FUNCTION Create_Dimension(sDimName IN VARCHAR, sTableName IN VARCHAR DEFAULT NULL, sDescr IN VARCHAR DEFAULT NULL) RETURN NUMBER;

END DAM_CORE;
/



DROP PACKAGE BODY DAM.DAM_CORE;

CREATE OR REPLACE PACKAGE BODY DAM."DAM_CORE" AS
/******************************************************************************
   NAME:       DAM_CORE
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        12.12.2007  FUCHSST          1. Created this package body.
******************************************************************************/

  FUNCTION Create_Dimension(sDimName IN VARCHAR, sTableName IN VARCHAR DEFAULT NULL, sDescr IN VARCHAR DEFAULT NULL) RETURN NUMBER IS
    tmpVar      NUMBER;
    tmpTblCount NUMBER;
    tmpReturn   NUMBER;
  BEGIN
    tmpVar := 0;        
    
    /* Insert Rows into DAM_DIMESNIONS and DAM_DIMENSION_FIELDS */
    SELECT COUNT(*) INTO tmpTblCount FROM dam_dimensions WHERE dim_name=sDimName;    
    IF tmpTblCount=0 THEN

      INSERT INTO dam_dimensions (dim_name, table_name, description) 
      VALUES (sDimName, sTableName, sDescr);
      
      SELECT DAM_DIMENSIONS_ID.CURRVAL INTO tmpVar FROM dual;
      
      INSERT INTO dam_dimension_fields (field_name, db_field_name, dimension_fk, seq) 
      VALUES ('id', 'id', tmpVar, -2);
      
      INSERT INTO dam_dimension_fields (field_name, db_field_name, dimension_fk, seq) 
      VALUES ('description', 'description', tmpVar, -1);
      COMMIT;
      
        /* Create physical table if not already present */
        SELECT COUNT(*) INTO tmpTblCount FROM user_tables WHERE table_name=sTableName;
        IF tmpTblCount=0 THEN
          EXECUTE IMMEDIATE 'CREATE TABLE ' || sTableName || ' ' ||
            '(id INT                  CONSTRAINT PK_' || sTableName || ' PRIMARY KEY, ' ||
            ' description VARCHAR(32) CONSTRAINT UQ_DESCR_' || sTableName || ' UNIQUE)';
        END IF;    
    END IF;                                                      
      
    
  END;
  

END DAM_CORE;
/



DROP VIEW DAM.DAM_FUNCTION_REF_LVL;

/* Formatted on 2010/03/19 21:24 (Formatter Plus v4.8.8) */
CREATE OR REPLACE FORCE VIEW dam.dam_function_ref_lvl (function_id,
                                                       uses_function_fk,
                                                       lvl
                                                      )
AS
   SELECT   d.function_id, d.uses_function_fk, MAX (d.lvl) lvl
       FROM (SELECT     f.function_id, fr.uses_function_fk, LEVEL lvl
                   FROM dam_function_ref fr, dam_functions f
                  WHERE f.function_id = fr.function_fk
             CONNECT BY PRIOR fr.function_fk = fr.uses_function_fk
             UNION
             SELECT f.function_id, NULL, 0 lvl
               FROM dam_function_ref fr, dam_functions f
              WHERE f.function_id = fr.function_fk(+)
                    AND fr.function_fk IS NULL) d
   GROUP BY d.function_id, d.uses_function_fk
   ORDER BY MAX (d.lvl), d.function_id;


